import mysql.connector
from mysql.connector import Error
from datetime import datetime

# Buffer where data to upload is stored if the database is unavailable
offlineUploadBuffer = []

WIDTH_NUM_DECIMALS = 4 # The number of decimals to round the width of the dogbone to.
WIDTH_THRESH = 0.0001
LENGTH_THRESH = 0.0001
DELTA_S_THRESH = 0.00001

# Tests to see if a simulation exists for the given parameters. Used to test if a simulation should be run or skipped.
def simulationExists(location, length, width, delta_s, sub_material):
    global mydb
    
    try:
        query = "SELECT * from dogbone_simulations WHERE location = %s AND length BETWEEN %s AND %s AND width BETWEEN %s AND %s AND delta_s BETWEEN %s AND %s AND substrate_material = %s;"
        vals = (location, length-LENGTH_THRESH, length+LENGTH_THRESH, width-WIDTH_THRESH, width+WIDTH_THRESH, 0, delta_s+DELTA_S_THRESH, sub_material)
        
        mycursor = mydb.cursor()
        mycursor.execute(query, vals)
        results = mycursor.fetchall()
        mydb.commit()
        mycursor.close()
        
        return len(results) > 0
    except Error as e:
        print("Database unavailable, assuming simulation should run.",e)
        return False

# Uploads the results of a simulation to the database
def uploadResults(width, length, z_im, trace, converged, delta_s, time, sub_material):
    global mydb
    global offlineUploadBuffer
    
    query = "INSERT INTO dogbone_simulations(width,length,z,location,converged,delta_s,sim_time,substrate_material) VALUES(%s,%s,%s,%s,%s,%s,%s,%s);"
    vals = (str(round(width, WIDTH_NUM_DECIMALS)), str(round(length, WIDTH_NUM_DECIMALS)), str(z_im), trace,
                         'Y' if converged else 'N', delta_s, time, sub_material)
    
    # Try uploading results to the server
    try:
        mycursor = mydb.cursor()
        mycursor.execute(query, vals)
        
        mydb.commit()
        mycursor.close()
        
    # If it fails (i.e. the server isn't available)
    except Error as e:
        print("Database unavailable, caching simulation data and retrying database connection.",e)
        
        addToBuffer((str(datetime.now()),) + vals)    # Add results of last simulation to the cache
        
        # reconnect to server and upload cache/buffer is successful
        disconnect()
        if(connect()):
            print("Connection to Database restablished.")
            uploadBuffer()
        else:
            print("Unable to connect to database. Results have been cached and will be uploaded when the database is available.",e)

# Uploads the cached data to the database server. Data is cached if a simulation is completed and the
# database server is not available.
def uploadBuffer():
    global mydb
    global offlineUploadBuffer
    
    buffSize = len(offlineUploadBuffer) # Length of buffer
    
    if buffSize == 0: return           # Return if the buffer is empty
    
    # Build the query string
    query = "INSERT INTO dogbone_simulations(simulated_at,width,length,z,location,converged,delta_s,sim_time,substrate_material) VALUES"
    query += str(offlineUploadBuffer[0])
    
    for i in range(1, buffSize):
        query += ","+str(offlineUploadBuffer[i])
    
    try:
        # Execute the query
        mycursor = mydb.cursor()
        mycursor.execute(query)
        mydb.commit()
        mycursor.close()
        
        offlineUploadBuffer.clear()             # Clear the RAM cache once we know the data has been uploaded
        os.remove("offlineUploadBuffer.dat")    # Clear the non-volatile data file
        print("Cache has been uploaded to database.")
    except Error as e:
        print("SEVERE: Error uploading cache to database after successful reconnetion. Cache is safe.", e)

def addToBuffer(vals):
    global offlineUploadBuffer
    
    # Add to RAM buffer
    offlineUploadBuffer.append((datetime.now(),) + vals)
    
    # Just in case the program crashes (or worse!!), also append the results to a file.
    f = open("offlineUploadBuffer.dat", "a+")
    f.write(str(vals))
    f.close()

# Connects to the database server
def connect():
    global mydb
    try:
        mydb = mysql.connector.connect(
                host    = "bmckean.dynu.net",
                user    = "msurf-script",
                passwd  = "Capstone#2019",
                database= "metasurface_design")
        return mydb.is_connected()
    except Error as e:
        return False

#disconnects from the database server.
def disconnect():
    global mydb
    if(mydb.is_connected()):
        mydb.close()
        print("Closed SQL Connection")